package com.lohit.dbtest;

import java.sql.Array;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DbController extends SQLiteOpenHelper {

	public static final String PREFS_NAME = "LoginCredentials";

	private static final String DATABASE_NAME = "doctor.db";
	
	private static final int DATABASE_VERSION = 1;
	
	AppointmentDetails details;

	ArrayList<AppointmentDetails> appointmentList = new ArrayList<AppointmentDetails>();

	public DbController(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		String create_table_doctor = "create table if not exists doctor(id integer primary key autoincrement, "
				+ "name text not null, "
				+ "username text not null, "
				+ "password text not null, "
				+ "spl text not null, "
				+ "email text not null, " + "location text not null );";

		String create_table_patient = "create table if not exists patient (id integer primary key autoincrement, "
				+ "name text not null, "
				+ "age integer not null, "
				+ "gender text not null, "
				+ "bgrp text , "
				+ "number text , "
				+ "address text not null );";

		String create_table_avail = "create table if not exists avail ("
				+ "date text primary key not null, "
				+ "slot1 integer not null, " + "slot2 integer not null, "
				+ "slot3 integer not null, " + "slot4 integer not null, "
				+ "slot5 integer not null,  " + "slot6 integer not null, "
				+ "slot7 integer not null,  " + "slot8 integer not null, "
				+ "slot9 integer not null, " + "slot10 integer not null, "
				+ "slot11 integer not null,  " + "slot12 integer not null, "
				+ "slot13 integer not null,  " + "slot14 integer not null, "
				+ "slot15 integer not null,  " + "slot16 integer not null, "
				+ "slot17 integer not null,  " + "slot18 integer not null, "
				+ "slot19 integer not null,  " + "slot20 integer not null "
				+");";

		db.execSQL(create_table_doctor);

		db.execSQL(create_table_patient);

		db.execSQL(create_table_avail);

		db.execSQL("CREATE TABLE " + Appointment.TABLE_NAME + " ("
				+ Appointment.ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"

				+ Appointment.PATIENT_ID + " INTEGER," + Appointment.DATE
				+ " DATE," + Appointment.TIME + " TEXT," + Appointment.CAUSE
				+ " TEXT,"

				+ Appointment.LOCATION + " TEXT,"

				+ Appointment.STATUS + " TEXT);");

		Log.d("db", "created");

		String insert_default = "insert into doctor(name, username, password, spl ,email, location) values ('Lohit', 'lohit56', 'lohit123', 'orthopedic', 'lohit@gmail.com','Chicalim' );";

		db.execSQL(insert_default);

		Log.d(" insert", "record inserted");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public HashMap<String, String> loginUser(String username, String password) {

		SQLiteDatabase database = this.getWritableDatabase();

		String selectQuery = "SELECT * FROM doctor where username='" + username
				+ "' and password='" + password + "';";

		Cursor cursor = database.rawQuery(selectQuery, null);

		if (cursor.moveToNext()) {

			HashMap<String, String> result = new HashMap<String, String>();

			String id = cursor.getString(0);
			result.put("id", id);

			String name = cursor.getString(1);
			result.put("name", name);

			cursor.close();

			return result;

		}
		cursor.close();
		return null;
	}

	public long createPatient(String name, String age, String gender,
			String bgrp, String ph_number, String address) {

		ContentValues cv = new ContentValues();

		cv.put(Patient.NAME, name);
		cv.put(Patient.AGE, age);
		cv.put(Patient.GENDER, gender);
		cv.put(Patient.BGRP, bgrp);
		cv.put(Patient.PH_NUMBER, ph_number);
		cv.put(Patient.ADDRESS, address);

		SQLiteDatabase sd = getWritableDatabase();

		long result = sd.insert(Patient.TABLE_NAME, null, cv);

		return result;

	}

	public String[] getAllPatients() {

		SQLiteDatabase sd = getWritableDatabase();

		String[] columns = new String[] { Patient.NAME };

		Cursor c = sd.query(Patient.TABLE_NAME, columns, null, null, null,
				null, null);

		if (c.getCount() > 0) {
			String[] str = new String[c.getCount()];
			int i = 0;

			while (c.moveToNext()) {
				str[i] = c.getString(c.getColumnIndex(Patient.NAME));
				i++;
			}
			c.close();
			sd.close();
			return str;
		} else {
			return new String[] {};
		}

	}

	public long createAppointment(String pid, String cause, String date,
			String time, String loc, String status) {

		ContentValues cv = new ContentValues();

		cv.put(Appointment.PATIENT_ID, pid);
		cv.put(Appointment.CAUSE, cause);
		cv.put(Appointment.DATE, date);
		cv.put(Appointment.TIME, time);
		cv.put(Appointment.LOCATION, loc);
		cv.put(Appointment.STATUS, status);

		SQLiteDatabase sd = getWritableDatabase();

		long result = sd.insert(Appointment.TABLE_NAME, null, cv);
		sd.close();
		return result;

	}

	public Long getPatientId(String patientName) {

		String sql = "select * from patient where name='" + patientName + "';";
		Long lid = null;
		SQLiteDatabase sd = getWritableDatabase();

		Cursor c = sd.rawQuery(sql, null);

		if (c.moveToNext()) {

			String id = c.getString(0);

			lid = Long.parseLong(id);
			c.close();
			sd.close();

			return lid;

		} else {

			lid = (long) -1;
			c.close();
			sd.close();
			return lid;
		}

	}

	public String getPatientNumber(String id) {

		String sql = "select * from patient where id='" + id + "';";

		String phoneNumber = null;

		SQLiteDatabase sd = getWritableDatabase();

		Cursor c = sd.rawQuery(sql, null);

		if (c.moveToNext()) {

			phoneNumber = c.getString(5);

		}

		c.close();
		sd.close();

		return phoneNumber;

	}

	public String getPatientIdFromAppointments(String aid) {
		String sql = "select patient_id from appointment where id='" + aid
				+ "';";
		String pid = null;
		SQLiteDatabase sd = getWritableDatabase();
		Cursor c = sd.rawQuery(sql, null);
		if (c.moveToNext()) {
			pid = c.getString(0);
		}
		c.close();
		sd.close();
		return pid;
	}

	@SuppressWarnings("unchecked")
	@SuppressLint("SimpleDateFormat")
	public ArrayList<AppointmentDetails> getAppointmentList(
			String dateOfAppointment) {

		SQLiteDatabase sd = getWritableDatabase();

		String tsql = "select appointment.id ,patient.name ,appointment.date ,appointment.time ,appointment.status ,patient.number ,appointment.cause ,appointment.location  from patient , appointment where patient.id=appointment.patient_id and appointment.status='0'and appointment.date='"
				+ dateOfAppointment + "';";

		Cursor c = sd.rawQuery(tsql, null);

		while (c.moveToNext()) {

			String appointment_id = c.getString(0);

			long aid = Long.parseLong(appointment_id);

			String pName = c.getString(1);
			String date = c.getString(2);
			String time = c.getString(3);
			String appointment_status = c.getString(4);

			String number = c.getString(5);
			String cause = c.getString(6);

			Log.d("cause", cause);

			String location = c.getString(7);

			int status = Integer.parseInt(appointment_status);

			details = new AppointmentDetails();

			details.setAid(aid);
			details.setPatientName(pName);

			try {

				Log.d("Date ", date + " " + time);

				SimpleDateFormat dateFormat = new SimpleDateFormat(
						"dd/MM/yyyy hh:mm:ss");

				Date date1 = dateFormat.parse(date + " " + time);

				details.setAppDate(date1);

			} catch (ParseException e) {

				Log.d("error", "Error conveting String to date...");
				e.printStackTrace();
			}

			details.setStatus(status);

			details.setIllness(cause);

			Log.d("illness", details.getIllness());
			details.setPatientNumber(number);
			details.setaLocation(location);

			appointmentList.add(details);

		}

		c.close();
		sd.close();

		Collections.sort(appointmentList);

		return appointmentList;

	}

	public void setStatus(String aid) {

		SQLiteDatabase sd = getWritableDatabase();

		ContentValues value = new ContentValues();

		value.put(Appointment.STATUS, "1");

		sd.update(Appointment.TABLE_NAME, value, Appointment.ID + "=" + aid,
				null);

		sd.close();

	}

		public int reschedule(String aid, String date, String time) {

		SQLiteDatabase sd = getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put(Appointment.DATE, date);
		values.put(Appointment.TIME, time);

		int i = sd.update(Appointment.TABLE_NAME, values, Appointment.ID + "="
				+ aid, null);

		sd.close();
		return i;

	}

	public String getPatientName(String pid) {

		String sql = "select name from patient where id='" + pid + "';";

		String patientName = null;

		SQLiteDatabase sd = getWritableDatabase();

		Cursor c = sd.rawQuery(sql, null);

		if (c.moveToNext()) {

			patientName = c.getString(0);

		}

		c.close();
		sd.close();

		return patientName;

	}

	ArrayList<String> getSlots(String date) {

		ArrayList<String> slots = new ArrayList<String>();

		SQLiteDatabase sd = getWritableDatabase();

		final String SQL_STATEMENT = "SELECT * FROM avail WHERE date=?";

		Cursor cursor = sd.rawQuery(SQL_STATEMENT, new String[] { date });

		if (cursor.moveToFirst()) {

			slots.clear();

			if (cursor.getString(1).equals("0")) {

				slots.add("09:30 AM");

			}

			if (cursor.getString(2).equals("0")) {

				slots.add("10:00 AM");

			}

			if (cursor.getString(3).equals("0")) {

				slots.add("10:30 AM");

			}
			if (cursor.getString(4).equals("0")) {

				slots.add("11:00 AM");

			}
			if (cursor.getString(5).equals("0")) {

				slots.add("11:30 AM");

			}
			if (cursor.getString(6).equals("0")) {
				
				slots.add("12:00 PM");
				
			}
			if (cursor.getString(7).equals("0")) {
				
				slots.add("12:30 PM");
				
			}
			if (cursor.getString(8).equals("0")) {
				
				slots.add("01:00 PM");
				
			}
			if (cursor.getString(9).equals("0")) {
				
				slots.add("01:30 PM");
				
			}
			if (cursor.getString(10).equals("0")) {
				
				slots.add("03:00 PM");
				
			}
			if (cursor.getString(11).equals("0")) {
				
				slots.add("03:30 PM");
				
			}
			if (cursor.getString(12).equals("0")) {
				
				slots.add("04:00 PM");
				
			}
			if (cursor.getString(13).equals("0")) {
				
				slots.add("04:30 PM");
				
			}
			if (cursor.getString(14).equals("0")) {
				
				slots.add("05:00 PM");
				
			}
			if (cursor.getString(15).equals("0")) {
				
				slots.add("05:30 PM");
				
			}
			if (cursor.getString(16).equals("0")) {
				
				slots.add("06:00 PM");
				
			}
			if (cursor.getString(17).equals("0")) {
				
				slots.add("06:30 PM");
				
			}
			if (cursor.getString(18).equals("0")) {
				
				slots.add("07:00 PM");
				
			}
			if (cursor.getString(19).equals("0")) {
				
				slots.add("07:30 PM");
				
			}
			if (cursor.getString(20).equals("0")) {
				
				slots.add("08:00 PM");
				
			}
			

			cursor.close();
			sd.close();
			return slots;

		} else {

			cursor.close();

			ContentValues contentValues = new ContentValues();

			contentValues.put("date", date);
			contentValues.put("slot1", "0");
			contentValues.put("slot2", "0");
			contentValues.put("slot3", "0");
			contentValues.put("slot4", "0");
			contentValues.put("slot5", "0");
			contentValues.put("slot6", "0");
			contentValues.put("slot7", "0");
			contentValues.put("slot8", "0");
			contentValues.put("slot9", "0");
			contentValues.put("slot10", "0");
			contentValues.put("slot11", "0");
			contentValues.put("slot12", "0");
			contentValues.put("slot13", "0");
			contentValues.put("slot14", "0");
			contentValues.put("slot15", "0");
			contentValues.put("slot16", "0");
			contentValues.put("slot17", "0");
			contentValues.put("slot18", "0");
			contentValues.put("slot19", "0");
			contentValues.put("slot20", "0");
			
			
			
			
			
			sd.insert("avail", null, contentValues);

			slots.add("09:30 AM");
			slots.add("10:00 AM");
			slots.add("10:30 AM");
			slots.add("11:00 AM");
			slots.add("11:30 AM");
			slots.add("12:00 PM");
			slots.add("12:30 PM");
			slots.add("01:00 PM");
			slots.add("01:30 PM");
			slots.add("03:00 PM");
			slots.add("03:30 PM");
			slots.add("04:00 PM");
			slots.add("04:30 PM");
			slots.add("05:00 PM");
			slots.add("05:30 PM");
			slots.add("06:00 PM");
			slots.add("06:30 PM");
			slots.add("07:00 PM");
			slots.add("07:30 PM");
			slots.add("08:00 PM");
			
			sd.close();

			sd.close();
			return slots;

		}

	}
	
	public int setSlot(String dateS,  String slot) {

		/*
		UPDATE Persons
				SET Address='Nissestien 67', City='Sandnes'
				WHERE LastName='Tjessem' AND FirstName='Jakob'*/
		
		
		SQLiteDatabase sd = getWritableDatabase();

	/*	ContentValues values = new ContentValues();*/

	String sql = "update avail set "+slot+"='1' where date='" + dateS + "'";
			
	/*		
		values.put(slot, "1");

		int i = sd.update("avail", values, "date"+ "="+ dateS, null);*/
	
	
	 
	sd.execSQL(sql);

		sd.close();
		return 1;

	}
	public int uncheckSlot(String dateS,  String slot) {
		
		/*
		UPDATE Persons
				SET Address='Nissestien 67', City='Sandnes'
				WHERE LastName='Tjessem' AND FirstName='Jakob'*/
		
		
		SQLiteDatabase sd = getWritableDatabase();
		
		/*	ContentValues values = new ContentValues();*/
		
		String sql = "update avail set "+slot+"='0' where date='" + dateS + "'";
		
		/*		
		values.put(slot, "1");

		int i = sd.update("avail", values, "date"+ "="+ dateS, null);*/
		
		
		
		sd.execSQL(sql);
		
		sd.close();
		return 1;
		
	}
	

	public String cancelAppointment(String aid) {
		SQLiteDatabase sq = getWritableDatabase();
		sq.delete(Appointment.TABLE_NAME, Appointment.ID + "=" + aid, null);
		/*String sql_stmt= "select appointment.time from avail where id='"+aid+"';";*/
		
		String sql_stmt="select * from avail";
		
		/*
		Cursor c=sq.rawQuery(sql_stmt, null);
		
		{
		
		}
			*/	
		
		/*Cursor c=sq.rawQuery(sql_stmt, new String[] {aid});
		if(c.isFirst()){		
		
		}*/
				
		sq.close();

		return null;
	}
	

}
